{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>18</td>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>28</td>\n",
       "      <td>32</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>25</td>\n",
       "      <td>30</td>\n",
       "      <td>35</td>\n",
       "      <td>40</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>18</td>\n",
       "      <td>24</td>\n",
       "      <td>30</td>\n",
       "      <td>36</td>\n",
       "      <td>42</td>\n",
       "      <td>48</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>14</td>\n",
       "      <td>21</td>\n",
       "      <td>28</td>\n",
       "      <td>35</td>\n",
       "      <td>42</td>\n",
       "      <td>49</td>\n",
       "      <td>56</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "      <td>24</td>\n",
       "      <td>32</td>\n",
       "      <td>40</td>\n",
       "      <td>48</td>\n",
       "      <td>56</td>\n",
       "      <td>64</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>18</td>\n",
       "      <td>27</td>\n",
       "      <td>36</td>\n",
       "      <td>45</td>\n",
       "      <td>54</td>\n",
       "      <td>63</td>\n",
       "      <td>72</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   1   2   3   4   5   6   7   8   9\n",
       "1  1   2   3   4   5   6   7   8   9\n",
       "2  2   4   6   8  10  12  14  16  18\n",
       "3  3   6   9  12  15  18  21  24  27\n",
       "4  4   8  12  16  20  24  28  32  36\n",
       "5  5  10  15  20  25  30  35  40  45\n",
       "6  6  12  18  24  30  36  42  48  54\n",
       "7  7  14  21  28  35  42  49  56  63\n",
       "8  8  16  24  32  40  48  56  64  72\n",
       "9  9  18  27  36  45  54  63  72  81"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel(r'C:\\Users\\Administrator\\Desktop\\工作簿1.xlsx',header=0,index_col=0)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>数据信息学院</th>\n",
       "      <th>建筑学院</th>\n",
       "      <th>动漫设计</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2020-10-10 08:00:00</td>\n",
       "      <td>89</td>\n",
       "      <td>50</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-10-10 09:00:00</td>\n",
       "      <td>82</td>\n",
       "      <td>30</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-10-10 10:00:00</td>\n",
       "      <td>46</td>\n",
       "      <td>82</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-10-10 11:00:00</td>\n",
       "      <td>18</td>\n",
       "      <td>99</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-10-10 12:00:00</td>\n",
       "      <td>17</td>\n",
       "      <td>58</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-10-10 13:00:00</td>\n",
       "      <td>29</td>\n",
       "      <td>38</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-10-10 14:00:00</td>\n",
       "      <td>58</td>\n",
       "      <td>73</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-10-10 15:00:00</td>\n",
       "      <td>70</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-10-10 16:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>11</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2020-10-10 17:00:00</td>\n",
       "      <td>71</td>\n",
       "      <td>63</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     数据信息学院  建筑学院  动漫设计\n",
       "2020-10-10 08:00:00      89    50     6\n",
       "2020-10-10 09:00:00      82    30     9\n",
       "2020-10-10 10:00:00      46    82    51\n",
       "2020-10-10 11:00:00      18    99    94\n",
       "2020-10-10 12:00:00      17    58    63\n",
       "2020-10-10 13:00:00      29    38    35\n",
       "2020-10-10 14:00:00      58    73    20\n",
       "2020-10-10 15:00:00      70     7     6\n",
       "2020-10-10 16:00:00      60    11    38\n",
       "2020-10-10 17:00:00      71    63    17"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import datetime as datetime\n",
    "\n",
    "data = {'数据信息学院':np.random.randint(1,100,10),\n",
    "        '建筑学院':np.random.randint(1,100,10),\n",
    "        '动漫设计':np.random.randint(1,100,10)}\n",
    "data_list=pd.date_range(start='202010100800',end='202010101700',freq='H')\n",
    "d5=pd.DataFrame(data,index=data_list)\n",
    "d5\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "d6 = d5.to_excel(r'C:\\Users\\Administrator\\Desktop\\新生报到统计.xlsx',sheet_name='新生报道统计')\n",
    "d6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(mysql+pymysql://root:***@127.0.0.1:3306/吴昊)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine=create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/吴昊?')\n",
    "engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>性别</th>\n",
       "      <th>出生日期</th>\n",
       "      <th>民族</th>\n",
       "      <th>籍贯</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>刘远</td>\n",
       "      <td>20191162</td>\n",
       "      <td>男</td>\n",
       "      <td>37301</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北天门</td>\n",
       "      <td>大数据1901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>梁奥云</td>\n",
       "      <td>20193660</td>\n",
       "      <td>男</td>\n",
       "      <td>36978</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北仙桃</td>\n",
       "      <td>大数据1901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>魏佳雪</td>\n",
       "      <td>20192661</td>\n",
       "      <td>女</td>\n",
       "      <td>37244</td>\n",
       "      <td>汉族</td>\n",
       "      <td>河南驻马店</td>\n",
       "      <td>大数据1901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>周文婷</td>\n",
       "      <td>20191002</td>\n",
       "      <td>女</td>\n",
       "      <td>37192</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北随州</td>\n",
       "      <td>大数据1901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>李鸿</td>\n",
       "      <td>20190834</td>\n",
       "      <td>男</td>\n",
       "      <td>37034</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北黄冈</td>\n",
       "      <td>大数据1901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>153</td>\n",
       "      <td>154</td>\n",
       "      <td>王永旺</td>\n",
       "      <td>20192872</td>\n",
       "      <td>男</td>\n",
       "      <td>36570</td>\n",
       "      <td>汉族</td>\n",
       "      <td>甘肃陇南</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>154</td>\n",
       "      <td>155</td>\n",
       "      <td>钟婧</td>\n",
       "      <td>20192624</td>\n",
       "      <td>女</td>\n",
       "      <td>36898</td>\n",
       "      <td>汉族</td>\n",
       "      <td>江西赣州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>155</td>\n",
       "      <td>156</td>\n",
       "      <td>崔豪</td>\n",
       "      <td>20192648</td>\n",
       "      <td>男</td>\n",
       "      <td>36313</td>\n",
       "      <td>汉族</td>\n",
       "      <td>河南郑州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>156</td>\n",
       "      <td>157</td>\n",
       "      <td>张炜民</td>\n",
       "      <td>20191000</td>\n",
       "      <td>男</td>\n",
       "      <td>36997</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北随州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>157</td>\n",
       "      <td>158</td>\n",
       "      <td>沈民珉</td>\n",
       "      <td>20192686</td>\n",
       "      <td>男</td>\n",
       "      <td>36077</td>\n",
       "      <td>苗族</td>\n",
       "      <td>贵州凯里</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>158 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      序号   姓名        学号 性别   出生日期  民族     籍贯       班级\n",
       "0      1   刘远  20191162  男  37301  汉族   湖北天门  大数据1901\n",
       "1      2  梁奥云  20193660  男  36978  汉族   湖北仙桃  大数据1901\n",
       "2      3  魏佳雪  20192661  女  37244  汉族  河南驻马店  大数据1901\n",
       "3      4  周文婷  20191002  女  37192  汉族   湖北随州  大数据1901\n",
       "4      5   李鸿  20190834  男  37034  汉族   湖北黄冈  大数据1901\n",
       "..   ...  ...       ... ..    ...  ..    ...      ...\n",
       "153  154  王永旺  20192872  男  36570  汉族   甘肃陇南  大数据1903\n",
       "154  155   钟婧  20192624  女  36898  汉族   江西赣州  大数据1903\n",
       "155  156   崔豪  20192648  男  36313  汉族   河南郑州  大数据1903\n",
       "156  157  张炜民  20191000  男  36997  汉族   湖北随州  大数据1903\n",
       "157  158  沈民珉  20192686  男  36077  苗族   贵州凯里  大数据1903\n",
       "\n",
       "[158 rows x 8 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_data = pd.read_sql_table('学生信息表',con=engine)\n",
    "sql_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>姓名</th>\n",
       "      <th>学号</th>\n",
       "      <th>性别</th>\n",
       "      <th>出生日期</th>\n",
       "      <th>民族</th>\n",
       "      <th>籍贯</th>\n",
       "      <th>班级</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>107</td>\n",
       "      <td>余梦露</td>\n",
       "      <td>20193657</td>\n",
       "      <td>女</td>\n",
       "      <td>37333</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北随州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>108</td>\n",
       "      <td>陈秋宇</td>\n",
       "      <td>20190617</td>\n",
       "      <td>男</td>\n",
       "      <td>37061</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北松滋</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>109</td>\n",
       "      <td>谢小冬</td>\n",
       "      <td>20190995</td>\n",
       "      <td>男</td>\n",
       "      <td>36838</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北黄冈</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>110</td>\n",
       "      <td>李锴</td>\n",
       "      <td>20192896</td>\n",
       "      <td>男</td>\n",
       "      <td>37545</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖南常德</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>111</td>\n",
       "      <td>李凯</td>\n",
       "      <td>20192873</td>\n",
       "      <td>男</td>\n",
       "      <td>37078</td>\n",
       "      <td>汉族</td>\n",
       "      <td>甘肃庆阳</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>112</td>\n",
       "      <td>王小强</td>\n",
       "      <td>20192656</td>\n",
       "      <td>男</td>\n",
       "      <td>36901</td>\n",
       "      <td>汉族</td>\n",
       "      <td>河南漯河</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>113</td>\n",
       "      <td>何帅龙</td>\n",
       "      <td>20192660</td>\n",
       "      <td>男</td>\n",
       "      <td>36999</td>\n",
       "      <td>汉族</td>\n",
       "      <td>河南周口</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>114</td>\n",
       "      <td>段辛海</td>\n",
       "      <td>20193634</td>\n",
       "      <td>男</td>\n",
       "      <td>36770</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北黄石</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>115</td>\n",
       "      <td>梁芳玉</td>\n",
       "      <td>20192839</td>\n",
       "      <td>女</td>\n",
       "      <td>37015</td>\n",
       "      <td>壮族</td>\n",
       "      <td>广西横县</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>116</td>\n",
       "      <td>胡华隆</td>\n",
       "      <td>20190988</td>\n",
       "      <td>男</td>\n",
       "      <td>37129</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北孝感</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>117</td>\n",
       "      <td>王京奥</td>\n",
       "      <td>20191272</td>\n",
       "      <td>男</td>\n",
       "      <td>37097</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北钟祥</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>118</td>\n",
       "      <td>查慧芬</td>\n",
       "      <td>20192823</td>\n",
       "      <td>女</td>\n",
       "      <td>36787</td>\n",
       "      <td>汉族</td>\n",
       "      <td>安徽安庆</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>12</td>\n",
       "      <td>119</td>\n",
       "      <td>郭荆威</td>\n",
       "      <td>20193304</td>\n",
       "      <td>男</td>\n",
       "      <td>37188</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北黄冈</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>13</td>\n",
       "      <td>120</td>\n",
       "      <td>陈太浩</td>\n",
       "      <td>20190975</td>\n",
       "      <td>男</td>\n",
       "      <td>36609</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北十堰</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14</td>\n",
       "      <td>121</td>\n",
       "      <td>王童飞</td>\n",
       "      <td>20190766</td>\n",
       "      <td>男</td>\n",
       "      <td>37034</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北十堰</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>15</td>\n",
       "      <td>122</td>\n",
       "      <td>王贤东</td>\n",
       "      <td>20193631</td>\n",
       "      <td>男</td>\n",
       "      <td>36664</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北黄石</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>16</td>\n",
       "      <td>123</td>\n",
       "      <td>刘耀威</td>\n",
       "      <td>20190980</td>\n",
       "      <td>男</td>\n",
       "      <td>37158</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北钟祥</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>17</td>\n",
       "      <td>124</td>\n",
       "      <td>安圣伟</td>\n",
       "      <td>20193642</td>\n",
       "      <td>男</td>\n",
       "      <td>37032</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北孝感</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>18</td>\n",
       "      <td>125</td>\n",
       "      <td>刘美奇</td>\n",
       "      <td>20193648</td>\n",
       "      <td>男</td>\n",
       "      <td>37240</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北荆州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>19</td>\n",
       "      <td>126</td>\n",
       "      <td>李威</td>\n",
       "      <td>20190964</td>\n",
       "      <td>男</td>\n",
       "      <td>36730</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北武汉</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>20</td>\n",
       "      <td>127</td>\n",
       "      <td>魏巧玲</td>\n",
       "      <td>20192664</td>\n",
       "      <td>女</td>\n",
       "      <td>37061</td>\n",
       "      <td>汉族</td>\n",
       "      <td>河南信阳</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>21</td>\n",
       "      <td>128</td>\n",
       "      <td>谢泽洺</td>\n",
       "      <td>20192687</td>\n",
       "      <td>男</td>\n",
       "      <td>36695</td>\n",
       "      <td>汉族</td>\n",
       "      <td>贵州麻江</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>22</td>\n",
       "      <td>129</td>\n",
       "      <td>常志龙</td>\n",
       "      <td>20193636</td>\n",
       "      <td>男</td>\n",
       "      <td>36801</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北襄阳</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>23</td>\n",
       "      <td>130</td>\n",
       "      <td>董智浩</td>\n",
       "      <td>20190765</td>\n",
       "      <td>男</td>\n",
       "      <td>37466</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北十堰</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>24</td>\n",
       "      <td>131</td>\n",
       "      <td>杨珊丽</td>\n",
       "      <td>20192837</td>\n",
       "      <td>女</td>\n",
       "      <td>36706</td>\n",
       "      <td>汉族</td>\n",
       "      <td>广西桂平</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25</td>\n",
       "      <td>132</td>\n",
       "      <td>顾自建</td>\n",
       "      <td>20193649</td>\n",
       "      <td>男</td>\n",
       "      <td>37251</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北荆州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>26</td>\n",
       "      <td>133</td>\n",
       "      <td>石润凯</td>\n",
       "      <td>20192649</td>\n",
       "      <td>男</td>\n",
       "      <td>36558</td>\n",
       "      <td>汉族</td>\n",
       "      <td>河南新密</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>27</td>\n",
       "      <td>134</td>\n",
       "      <td>黄涛</td>\n",
       "      <td>20190818</td>\n",
       "      <td>男</td>\n",
       "      <td>37139</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北黄冈</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>28</td>\n",
       "      <td>135</td>\n",
       "      <td>陈迪冲</td>\n",
       "      <td>20191174</td>\n",
       "      <td>男</td>\n",
       "      <td>36996</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北丹江口</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>29</td>\n",
       "      <td>136</td>\n",
       "      <td>魏丽君</td>\n",
       "      <td>20191172</td>\n",
       "      <td>男</td>\n",
       "      <td>36107</td>\n",
       "      <td>汉族</td>\n",
       "      <td>山西朔州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>30</td>\n",
       "      <td>137</td>\n",
       "      <td>徐星禹</td>\n",
       "      <td>20192825</td>\n",
       "      <td>男</td>\n",
       "      <td>36874</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖南岳阳</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>31</td>\n",
       "      <td>138</td>\n",
       "      <td>杨永君</td>\n",
       "      <td>20192663</td>\n",
       "      <td>女</td>\n",
       "      <td>36932</td>\n",
       "      <td>汉族</td>\n",
       "      <td>河南信阳</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>32</td>\n",
       "      <td>139</td>\n",
       "      <td>邹熙健</td>\n",
       "      <td>20190983</td>\n",
       "      <td>男</td>\n",
       "      <td>37322</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北钟祥</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>33</td>\n",
       "      <td>140</td>\n",
       "      <td>吴昊</td>\n",
       "      <td>20193659</td>\n",
       "      <td>男</td>\n",
       "      <td>36798</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北随州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>34</td>\n",
       "      <td>141</td>\n",
       "      <td>李赞敏</td>\n",
       "      <td>20192623</td>\n",
       "      <td>男</td>\n",
       "      <td>37205</td>\n",
       "      <td>汉族</td>\n",
       "      <td>江西九江</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>35</td>\n",
       "      <td>142</td>\n",
       "      <td>张天佑</td>\n",
       "      <td>20193637</td>\n",
       "      <td>男</td>\n",
       "      <td>36864</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北襄阳</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>36</td>\n",
       "      <td>143</td>\n",
       "      <td>李偲</td>\n",
       "      <td>20190993</td>\n",
       "      <td>女</td>\n",
       "      <td>36835</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北黄冈</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>37</td>\n",
       "      <td>144</td>\n",
       "      <td>孙悦</td>\n",
       "      <td>20192676</td>\n",
       "      <td>女</td>\n",
       "      <td>37119</td>\n",
       "      <td>汉族</td>\n",
       "      <td>河南固始</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>38</td>\n",
       "      <td>145</td>\n",
       "      <td>钟磊</td>\n",
       "      <td>20172505</td>\n",
       "      <td>男</td>\n",
       "      <td>36312</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北孝感</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>39</td>\n",
       "      <td>146</td>\n",
       "      <td>吴昊霖</td>\n",
       "      <td>20194710</td>\n",
       "      <td>男</td>\n",
       "      <td>36736</td>\n",
       "      <td>汉族</td>\n",
       "      <td>山西运城</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>40</td>\n",
       "      <td>147</td>\n",
       "      <td>郭梦凡</td>\n",
       "      <td>20192779</td>\n",
       "      <td>女</td>\n",
       "      <td>37064</td>\n",
       "      <td>汉族</td>\n",
       "      <td>河北保定</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>41</td>\n",
       "      <td>148</td>\n",
       "      <td>屠新萌</td>\n",
       "      <td>20192829</td>\n",
       "      <td>男</td>\n",
       "      <td>36711</td>\n",
       "      <td>汉族</td>\n",
       "      <td>安徽霍邱</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>42</td>\n",
       "      <td>149</td>\n",
       "      <td>沈龙</td>\n",
       "      <td>20190965</td>\n",
       "      <td>男</td>\n",
       "      <td>36905</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北孝感</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>43</td>\n",
       "      <td>150</td>\n",
       "      <td>李宇航</td>\n",
       "      <td>20192831</td>\n",
       "      <td>男</td>\n",
       "      <td>36691</td>\n",
       "      <td>汉族</td>\n",
       "      <td>安徽省</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>44</td>\n",
       "      <td>151</td>\n",
       "      <td>石柳</td>\n",
       "      <td>20193630</td>\n",
       "      <td>女</td>\n",
       "      <td>37212</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北武汉</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>45</td>\n",
       "      <td>152</td>\n",
       "      <td>张如潮</td>\n",
       "      <td>20192871</td>\n",
       "      <td>男</td>\n",
       "      <td>36672</td>\n",
       "      <td>汉族</td>\n",
       "      <td>甘肃天水</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>46</td>\n",
       "      <td>153</td>\n",
       "      <td>李龙斌</td>\n",
       "      <td>20192833</td>\n",
       "      <td>男</td>\n",
       "      <td>43940</td>\n",
       "      <td>汉族</td>\n",
       "      <td>广西平南</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>47</td>\n",
       "      <td>154</td>\n",
       "      <td>王永旺</td>\n",
       "      <td>20192872</td>\n",
       "      <td>男</td>\n",
       "      <td>36570</td>\n",
       "      <td>汉族</td>\n",
       "      <td>甘肃陇南</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>48</td>\n",
       "      <td>155</td>\n",
       "      <td>钟婧</td>\n",
       "      <td>20192624</td>\n",
       "      <td>女</td>\n",
       "      <td>36898</td>\n",
       "      <td>汉族</td>\n",
       "      <td>江西赣州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49</td>\n",
       "      <td>156</td>\n",
       "      <td>崔豪</td>\n",
       "      <td>20192648</td>\n",
       "      <td>男</td>\n",
       "      <td>36313</td>\n",
       "      <td>汉族</td>\n",
       "      <td>河南郑州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50</td>\n",
       "      <td>157</td>\n",
       "      <td>张炜民</td>\n",
       "      <td>20191000</td>\n",
       "      <td>男</td>\n",
       "      <td>36997</td>\n",
       "      <td>汉族</td>\n",
       "      <td>湖北随州</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>51</td>\n",
       "      <td>158</td>\n",
       "      <td>沈民珉</td>\n",
       "      <td>20192686</td>\n",
       "      <td>男</td>\n",
       "      <td>36077</td>\n",
       "      <td>苗族</td>\n",
       "      <td>贵州凯里</td>\n",
       "      <td>大数据1903</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     序号   姓名        学号 性别   出生日期  民族     籍贯       班级\n",
       "0   107  余梦露  20193657  女  37333  汉族   湖北随州  大数据1903\n",
       "1   108  陈秋宇  20190617  男  37061  汉族   湖北松滋  大数据1903\n",
       "2   109  谢小冬  20190995  男  36838  汉族   湖北黄冈  大数据1903\n",
       "3   110   李锴  20192896  男  37545  汉族   湖南常德  大数据1903\n",
       "4   111   李凯  20192873  男  37078  汉族   甘肃庆阳  大数据1903\n",
       "5   112  王小强  20192656  男  36901  汉族   河南漯河  大数据1903\n",
       "6   113  何帅龙  20192660  男  36999  汉族   河南周口  大数据1903\n",
       "7   114  段辛海  20193634  男  36770  汉族   湖北黄石  大数据1903\n",
       "8   115  梁芳玉  20192839  女  37015  壮族   广西横县  大数据1903\n",
       "9   116  胡华隆  20190988  男  37129  汉族   湖北孝感  大数据1903\n",
       "10  117  王京奥  20191272  男  37097  汉族   湖北钟祥  大数据1903\n",
       "11  118  查慧芬  20192823  女  36787  汉族   安徽安庆  大数据1903\n",
       "12  119  郭荆威  20193304  男  37188  汉族   湖北黄冈  大数据1903\n",
       "13  120  陈太浩  20190975  男  36609  汉族   湖北十堰  大数据1903\n",
       "14  121  王童飞  20190766  男  37034  汉族   湖北十堰  大数据1903\n",
       "15  122  王贤东  20193631  男  36664  汉族   湖北黄石  大数据1903\n",
       "16  123  刘耀威  20190980  男  37158  汉族   湖北钟祥  大数据1903\n",
       "17  124  安圣伟  20193642  男  37032  汉族   湖北孝感  大数据1903\n",
       "18  125  刘美奇  20193648  男  37240  汉族   湖北荆州  大数据1903\n",
       "19  126   李威  20190964  男  36730  汉族   湖北武汉  大数据1903\n",
       "20  127  魏巧玲  20192664  女  37061  汉族   河南信阳  大数据1903\n",
       "21  128  谢泽洺  20192687  男  36695  汉族   贵州麻江  大数据1903\n",
       "22  129  常志龙  20193636  男  36801  汉族   湖北襄阳  大数据1903\n",
       "23  130  董智浩  20190765  男  37466  汉族   湖北十堰  大数据1903\n",
       "24  131  杨珊丽  20192837  女  36706  汉族   广西桂平  大数据1903\n",
       "25  132  顾自建  20193649  男  37251  汉族   湖北荆州  大数据1903\n",
       "26  133  石润凯  20192649  男  36558  汉族   河南新密  大数据1903\n",
       "27  134   黄涛  20190818  男  37139  汉族   湖北黄冈  大数据1903\n",
       "28  135  陈迪冲  20191174  男  36996  汉族  湖北丹江口  大数据1903\n",
       "29  136  魏丽君  20191172  男  36107  汉族   山西朔州  大数据1903\n",
       "30  137  徐星禹  20192825  男  36874  汉族   湖南岳阳  大数据1903\n",
       "31  138  杨永君  20192663  女  36932  汉族   河南信阳  大数据1903\n",
       "32  139  邹熙健  20190983  男  37322  汉族   湖北钟祥  大数据1903\n",
       "33  140   吴昊  20193659  男  36798  汉族   湖北随州  大数据1903\n",
       "34  141  李赞敏  20192623  男  37205  汉族   江西九江  大数据1903\n",
       "35  142  张天佑  20193637  男  36864  汉族   湖北襄阳  大数据1903\n",
       "36  143   李偲  20190993  女  36835  汉族   湖北黄冈  大数据1903\n",
       "37  144   孙悦  20192676  女  37119  汉族   河南固始  大数据1903\n",
       "38  145   钟磊  20172505  男  36312  汉族   湖北孝感  大数据1903\n",
       "39  146  吴昊霖  20194710  男  36736  汉族   山西运城  大数据1903\n",
       "40  147  郭梦凡  20192779  女  37064  汉族   河北保定  大数据1903\n",
       "41  148  屠新萌  20192829  男  36711  汉族   安徽霍邱  大数据1903\n",
       "42  149   沈龙  20190965  男  36905  汉族   湖北孝感  大数据1903\n",
       "43  150  李宇航  20192831  男  36691  汉族    安徽省  大数据1903\n",
       "44  151   石柳  20193630  女  37212  汉族   湖北武汉  大数据1903\n",
       "45  152  张如潮  20192871  男  36672  汉族   甘肃天水  大数据1903\n",
       "46  153  李龙斌  20192833  男  43940  汉族   广西平南  大数据1903\n",
       "47  154  王永旺  20192872  男  36570  汉族   甘肃陇南  大数据1903\n",
       "48  155   钟婧  20192624  女  36898  汉族   江西赣州  大数据1903\n",
       "49  156   崔豪  20192648  男  36313  汉族   河南郑州  大数据1903\n",
       "50  157  张炜民  20191000  男  36997  汉族   湖北随州  大数据1903\n",
       "51  158  沈民珉  20192686  男  36077  苗族   贵州凯里  大数据1903"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = 'select * from 学生信息表 where 班级 = \"大数据1903\";'\n",
    "lsp = pd.read_sql_query(sql,con=engine) \n",
    "lsp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>占地面积</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">河北省</td>\n",
       "      <td>石家庄市</td>\n",
       "      <td>15848.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>唐山市</td>\n",
       "      <td>13472.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>邯郸市</td>\n",
       "      <td>12073.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>秦皇岛市</td>\n",
       "      <td>7813.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"4\" valign=\"top\">河南省</td>\n",
       "      <td>郑州市</td>\n",
       "      <td>7446.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>开封市</td>\n",
       "      <td>6444.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>洛阳市</td>\n",
       "      <td>15230.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>新乡市</td>\n",
       "      <td>8269.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             占地面积\n",
       "河北省 石家庄市  15848.0\n",
       "    唐山市   13472.0\n",
       "    邯郸市   12073.8\n",
       "    秦皇岛市   7813.0\n",
       "河南省 郑州市    7446.0\n",
       "    开封市    6444.0\n",
       "    洛阳市   15230.0\n",
       "    新乡市    8269.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mulitindex_df=pd.DataFrame({'占地面积':[15848,13472,12073.8,7813,7446,6444,15230,8269]},\n",
    "   \t       index=[['河北省','河北省','河北省','河北省',\n",
    "   \t              '河南省','河南省','河南省','河南省'],\n",
    "   \t             ['石家庄市','唐山市','邯郸市','秦皇岛市',\n",
    "   \t             '郑州市','开封市','洛阳市','新乡市']])\n",
    "mulitindex_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>本</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td rowspan=\"3\" valign=\"top\">小说</td>\n",
       "      <td>高山上的小邮局</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>失踪的总统</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>绿毛水怪</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"3\" valign=\"top\">散文随笔</td>\n",
       "      <td>皮囊</td>\n",
       "      <td>94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>浮生六记</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>自在独行</td>\n",
       "      <td>101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td rowspan=\"3\" valign=\"top\">传记</td>\n",
       "      <td>梅西</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>老舍自传</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>库里传</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                本\n",
       "小说   高山上的小邮局   50\n",
       "     失踪的总统     60\n",
       "     绿毛水怪      40\n",
       "散文随笔 皮囊        94\n",
       "     浮生六记      63\n",
       "     自在独行     101\n",
       "传记   梅西       200\n",
       "     老舍自传      56\n",
       "     库里传       45"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'本':[50,60,40,94,63,101,200,56,45]},\n",
    "   \t        index=[['小说','小说','小说',\n",
    "                    '散文随笔','散文随笔','散文随笔',\n",
    "                    '传记','传记','传记'],\n",
    "                   ['高山上的小邮局','失踪的总统','绿毛水怪',\n",
    "                    '皮囊','浮生六记','自在独行',\n",
    "                    '梅西','老舍自传','库里传']])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">一本分数线</th>\n",
       "      <th colspan=\"2\" halign=\"left\">二本分数线</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>文科</th>\n",
       "      <th>理科</th>\n",
       "      <th>文科</th>\n",
       "      <th>理科</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>2018</td>\n",
       "      <td>576</td>\n",
       "      <td>532</td>\n",
       "      <td>488</td>\n",
       "      <td>432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2017</td>\n",
       "      <td>555</td>\n",
       "      <td>537</td>\n",
       "      <td>468</td>\n",
       "      <td>439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2016</td>\n",
       "      <td>583</td>\n",
       "      <td>548</td>\n",
       "      <td>532</td>\n",
       "      <td>494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2015</td>\n",
       "      <td>579</td>\n",
       "      <td>548</td>\n",
       "      <td>527</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2014</td>\n",
       "      <td>565</td>\n",
       "      <td>543</td>\n",
       "      <td>507</td>\n",
       "      <td>495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2013</td>\n",
       "      <td>549</td>\n",
       "      <td>550</td>\n",
       "      <td>494</td>\n",
       "      <td>505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2012</td>\n",
       "      <td>495</td>\n",
       "      <td>477</td>\n",
       "      <td>446</td>\n",
       "      <td>433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2011</td>\n",
       "      <td>524</td>\n",
       "      <td>484</td>\n",
       "      <td>481</td>\n",
       "      <td>435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2010</td>\n",
       "      <td>524</td>\n",
       "      <td>494</td>\n",
       "      <td>474</td>\n",
       "      <td>441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2009</td>\n",
       "      <td>532</td>\n",
       "      <td>501</td>\n",
       "      <td>489</td>\n",
       "      <td>459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2008</td>\n",
       "      <td>515</td>\n",
       "      <td>502</td>\n",
       "      <td>472</td>\n",
       "      <td>455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2007</td>\n",
       "      <td>528</td>\n",
       "      <td>531</td>\n",
       "      <td>489</td>\n",
       "      <td>478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2006</td>\n",
       "      <td>516</td>\n",
       "      <td>528</td>\n",
       "      <td>476</td>\n",
       "      <td>476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     一本分数线      二本分数线     \n",
       "        文科   理科    文科   理科\n",
       "2018   576  532   488  432\n",
       "2017   555  537   468  439\n",
       "2016   583  548   532  494\n",
       "2015   579  548   527  495\n",
       "2014   565  543   507  495\n",
       "2013   549  550   494  505\n",
       "2012   495  477   446  433\n",
       "2011   524  484   481  435\n",
       "2010   524  494   474  441\n",
       "2009   532  501   489  459\n",
       "2008   515  502   472  455\n",
       "2007   528  531   489  478\n",
       "2006   516  528   476  476"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1=pd.read_excel(r'C:\\Users\\Administrator\\Desktop\\scores.xlsx',header=[0,1],index_col=0)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(mysql+pymysql://root:***@127.0.0.1:3306/dsj?charset=utf8)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymysql\n",
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/dsj?charset=utf8')\n",
    "engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>detail_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>dishes_id</th>\n",
       "      <th>logicprn_name</th>\n",
       "      <th>parent_class_name</th>\n",
       "      <th>dishes_name</th>\n",
       "      <th>itemis_add</th>\n",
       "      <th>counts</th>\n",
       "      <th>amounts</th>\n",
       "      <th>cost</th>\n",
       "      <th>place_order_time</th>\n",
       "      <th>discount_amt</th>\n",
       "      <th>discount_reason</th>\n",
       "      <th>kick_back</th>\n",
       "      <th>add_inprice</th>\n",
       "      <th>add_info</th>\n",
       "      <th>bar_code</th>\n",
       "      <th>picture_file</th>\n",
       "      <th>emp_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2956</td>\n",
       "      <td>417</td>\n",
       "      <td>610062</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒜蓉生蚝</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:05:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/104001.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2958</td>\n",
       "      <td>417</td>\n",
       "      <td>609957</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒙古烤羊腿</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:07:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/202003.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2961</td>\n",
       "      <td>417</td>\n",
       "      <td>609950</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>大蒜苋菜</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:07:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/303001.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2966</td>\n",
       "      <td>417</td>\n",
       "      <td>610038</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>芝麻烤紫菜</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:11:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/105002.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2968</td>\n",
       "      <td>417</td>\n",
       "      <td>610003</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>蒜香包</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-01 11:11:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/503002.jpg</td>\n",
       "      <td>1442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2774</td>\n",
       "      <td>6750</td>\n",
       "      <td>774</td>\n",
       "      <td>610011</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>白饭/大碗</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 21:56:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/601005.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2775</td>\n",
       "      <td>6742</td>\n",
       "      <td>774</td>\n",
       "      <td>609996</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>牛尾汤</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 21:56:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/201006.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2776</td>\n",
       "      <td>6756</td>\n",
       "      <td>774</td>\n",
       "      <td>609949</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>意文柠檬汁</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:01:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/404005.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2777</td>\n",
       "      <td>6763</td>\n",
       "      <td>774</td>\n",
       "      <td>610014</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>金玉良缘</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:03:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/302003.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2778</td>\n",
       "      <td>6764</td>\n",
       "      <td>774</td>\n",
       "      <td>610017</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>酸辣藕丁</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>2016-08-10 22:04:00</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>0</td>\n",
       "      <td>NA</td>\n",
       "      <td>NA</td>\n",
       "      <td>caipu/302006.jpg</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2779 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     detail_id order_id dishes_id logicprn_name parent_class_name dishes_name  \\\n",
       "0         2956      417    610062            NA                NA        蒜蓉生蚝   \n",
       "1         2958      417    609957            NA                NA       蒙古烤羊腿   \n",
       "2         2961      417    609950            NA                NA        大蒜苋菜   \n",
       "3         2966      417    610038            NA                NA       芝麻烤紫菜   \n",
       "4         2968      417    610003            NA                NA         蒜香包   \n",
       "...        ...      ...       ...           ...               ...         ...   \n",
       "2774      6750      774    610011            NA                NA       白饭/大碗   \n",
       "2775      6742      774    609996            NA                NA         牛尾汤   \n",
       "2776      6756      774    609949            NA                NA      意文柠檬汁    \n",
       "2777      6763      774    610014            NA                NA        金玉良缘   \n",
       "2778      6764      774    610017            NA                NA        酸辣藕丁   \n",
       "\n",
       "     itemis_add  counts  amounts cost    place_order_time discount_amt  \\\n",
       "0             0     1.0     49.0   NA 2016-08-01 11:05:00           NA   \n",
       "1             0     1.0     48.0   NA 2016-08-01 11:07:00           NA   \n",
       "2             0     1.0     30.0   NA 2016-08-01 11:07:00           NA   \n",
       "3             0     1.0     25.0   NA 2016-08-01 11:11:00           NA   \n",
       "4             0     1.0     13.0   NA 2016-08-01 11:11:00           NA   \n",
       "...         ...     ...      ...  ...                 ...          ...   \n",
       "2774          0     1.0     10.0   NA 2016-08-10 21:56:00           NA   \n",
       "2775          0     1.0     40.0   NA 2016-08-10 21:56:00           NA   \n",
       "2776          0     1.0     13.0   NA 2016-08-10 22:01:00           NA   \n",
       "2777          0     1.0     30.0   NA 2016-08-10 22:03:00           NA   \n",
       "2778          0     1.0     33.0   NA 2016-08-10 22:04:00           NA   \n",
       "\n",
       "     discount_reason kick_back add_inprice add_info bar_code  \\\n",
       "0                 NA        NA           0       NA       NA   \n",
       "1                 NA        NA           0       NA       NA   \n",
       "2                 NA        NA           0       NA       NA   \n",
       "3                 NA        NA           0       NA       NA   \n",
       "4                 NA        NA           0       NA       NA   \n",
       "...              ...       ...         ...      ...      ...   \n",
       "2774              NA        NA           0       NA       NA   \n",
       "2775              NA        NA           0       NA       NA   \n",
       "2776              NA        NA           0       NA       NA   \n",
       "2777              NA        NA           0       NA       NA   \n",
       "2778              NA        NA           0       NA       NA   \n",
       "\n",
       "          picture_file emp_id  \n",
       "0     caipu/104001.jpg   1442  \n",
       "1     caipu/202003.jpg   1442  \n",
       "2     caipu/303001.jpg   1442  \n",
       "3     caipu/105002.jpg   1442  \n",
       "4     caipu/503002.jpg   1442  \n",
       "...                ...    ...  \n",
       "2774  caipu/601005.jpg   1138  \n",
       "2775  caipu/201006.jpg   1138  \n",
       "2776  caipu/404005.jpg   1138  \n",
       "2777  caipu/302003.jpg   1138  \n",
       "2778  caipu/302006.jpg   1138  \n",
       "\n",
       "[2779 rows x 19 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_data = pd.read_sql_table('meal_order_detail1',con=engine)\n",
    "sql_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>detail_id</th>\n",
       "      <th>order_id</th>\n",
       "      <th>dishes_id</th>\n",
       "      <th>logicprn_name</th>\n",
       "      <th>parent_class_name</th>\n",
       "      <th>dishes_name</th>\n",
       "      <th>itemis_add</th>\n",
       "      <th>counts</th>\n",
       "      <th>amounts</th>\n",
       "      <th>cost</th>\n",
       "      <th>place_order_time</th>\n",
       "      <th>discount_amt</th>\n",
       "      <th>discount_reason</th>\n",
       "      <th>kick_back</th>\n",
       "      <th>add_inprice</th>\n",
       "      <th>add_info</th>\n",
       "      <th>bar_code</th>\n",
       "      <th>picture_file</th>\n",
       "      <th>emp_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [detail_id, order_id, dishes_id, logicprn_name, parent_class_name, dishes_name, itemis_add, counts, amounts, cost, place_order_time, discount_amt, discount_reason, kick_back, add_inprice, add_info, bar_code, picture_file, emp_id]\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_data[:0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dishes_name\n",
       "百里香奶油烤紅酒牛肉         178.000000\n",
       "百里香奶油烤红酒牛肉         178.000000\n",
       "芝士烩波士顿龙虾           171.875000\n",
       "倒立蒸梭子蟹             169.000000\n",
       "52度泸州老窖            159.000000\n",
       "                      ...    \n",
       "农夫山泉NFC果汁100%橙汁      4.090909\n",
       " 北冰洋汽水               2.555556\n",
       "青岛啤酒罐装               2.394737\n",
       "哈尔滨啤酒罐装              2.172414\n",
       "白饭/小碗                0.320856\n",
       "Length: 145, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_data1=sql_data['amounts'].groupby(sql_data['dishes_name']).sum()/sql_data['counts'].groupby(sql_data['dishes_name']).sum()\n",
    "sql_data1=sql_data1.sort_values(ascending=False)\n",
    "sql_data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "白饭/大碗         92\n",
       "凉拌菠菜          77\n",
       "谷稻小庄          72\n",
       "麻辣小龙虾         65\n",
       "白饭/小碗         60\n",
       "              ..\n",
       "铁板牛肉           2\n",
       "三丝鳝鱼           2\n",
       "咖啡奶香面包         2\n",
       "百里香奶油烤紅酒牛肉     1\n",
       "冰镇花螺           1\n",
       "Name: dishes_name, Length: 145, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_data2=sql_data['dishes_name'].value_counts()\n",
    "sql_data2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "白饭/大碗        92\n",
       "凉拌菠菜         77\n",
       "谷稻小庄         72\n",
       "麻辣小龙虾        65\n",
       "白饭/小碗        60\n",
       "五色糯米饭(七色)    58\n",
       "焖猪手          55\n",
       "芝士烩波士顿龙虾     55\n",
       "辣炒鱿鱼         53\n",
       "水煮鱼          47\n",
       "Name: dishes_name, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_data2[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
